﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Web.UI.WebControls;

namespace ControlExplorer.samples.ConditionalFormatting
{
  public partial class Overview : SpreadDemoPage
  {
    protected void Page_Load(object sender, EventArgs e)
    {
      if (IsPostBack)
      {
        return;
      }

      InitSpread(FpSpread1);
      InitSpreadStyles(FpSpread1.Sheets[0]);
      this.FpSpread1.UseClipboard = false;
    }

    private void InitSpread(FarPoint.Web.Spread.FpSpread spread)
    {
      using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Northwind.mdb;Persist Security Info=True"))
      {
        using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT TOP 9 Products.ProductName as 产品, Products.UnitPrice as [单价], Products.UnitsOnOrder as [订单], Products.UnitsInStock as [存货] FROM Products", connection))
        {
          DataTable products = new DataTable("Products");
          adapter.Fill(products);
          FpSpread1.DataSource = products;
        }
      }

      spread.CssClass = "spreadStyle";
      spread.UseClipboard = false;
    }

    private void InitSpreadStyles(FarPoint.Web.Spread.SheetView sheet)
    {
      sheet.ColumnCount = 4;
      sheet.Columns[0].Width = 150;
      sheet.Columns[1].Width = 80;
      sheet.Columns[2].Width = 100;
      sheet.Columns[3].Width = 80;

      DataTable dt = (DataTable)sheet.DataSource;
      sheet.AddUnboundRows(sheet.RowCount, 1);
      FarPoint.Web.Spread.Cell cell = sheet.Cells[sheet.RowCount - 1, 0];
      cell.Value = "平均值:";

      FarPoint.Web.Spread.Row row = sheet.Rows[sheet.RowCount - 1];
      row.Locked = true;
      row.Font.Italic = true;
      row.BackColor = System.Drawing.Color.Yellow;
      row.Border.BorderColorTop = Color.Green;
      row.Border.BorderSizeTop = 2;
      row.Border.BorderStyleTop = BorderStyle.Solid;

      for (int i = 1; i < sheet.ColumnCount; i++)
      {
        string col = Convert.ToString((char)(65 + i));
        sheet.Cells[sheet.RowCount - 1, i].Formula = "AVERAGE(" + col + "1:" + col + Convert.ToString(sheet.RowCount - 1) + ")";
      }

      FarPoint.Web.Spread.ConditionalFormatting cf = new FarPoint.Web.Spread.ConditionalFormatting(new FarPoint.Web.Spread.Model.CellRange(0, 1, sheet.RowCount - 1, 1));
      FarPoint.Web.Spread.DatabarConditionalFormattingRule rule = new FarPoint.Web.Spread.DatabarConditionalFormattingRule();
      rule.BorderColor = System.Drawing.Color.Silver;
      rule.ShowBorder = true;
      rule.Gradient = true;
      rule.Minimum = new FarPoint.Web.Spread.ConditionalFormattingValue(0, FarPoint.Web.Spread.ConditionalFormattingValueType.Number);
      rule.Maximum = new FarPoint.Web.Spread.ConditionalFormattingValue(100, FarPoint.Web.Spread.ConditionalFormattingValueType.Max);
      cf.Add(rule);
      sheet.ConditionalFormatting.Add(cf);

      FarPoint.Web.Spread.ConditionalFormatting cf1 = new FarPoint.Web.Spread.ConditionalFormatting(new FarPoint.Web.Spread.Model.CellRange(0, 2, sheet.RowCount - 1, 2));
      FarPoint.Web.Spread.DatabarConditionalFormattingRule rule1 = new FarPoint.Web.Spread.DatabarConditionalFormattingRule();
      rule1.BorderColor = System.Drawing.Color.Silver;
      rule1.ShowBorder = true;
      rule1.Gradient = true;
      rule1.Minimum = new FarPoint.Web.Spread.ConditionalFormattingValue(0, FarPoint.Web.Spread.ConditionalFormattingValueType.Number);
      rule1.Maximum = new FarPoint.Web.Spread.ConditionalFormattingValue(120, FarPoint.Web.Spread.ConditionalFormattingValueType.Max);
      cf1.Add(rule1);
      sheet.ConditionalFormatting.Add(cf1);

      FarPoint.Web.Spread.ConditionalFormatting cf2 = new FarPoint.Web.Spread.ConditionalFormatting(new FarPoint.Web.Spread.Model.CellRange(0, 3, sheet.RowCount - 1, 3));
      FarPoint.Web.Spread.IconSetConditionalFormattingRule rule2 = new FarPoint.Web.Spread.IconSetConditionalFormattingRule(FarPoint.Web.Spread.ConditionalFormattingIconSetStyle.ThreeCircledSymbols);

      cf2.Add(rule2);
      sheet.ConditionalFormatting.Add(cf2);

      FarPoint.Web.Spread.ConditionalFormatting cf3 = new FarPoint.Web.Spread.ConditionalFormatting(new FarPoint.Web.Spread.Model.CellRange(sheet.RowCount - 1, 1, 1, sheet.ColumnCount));
      FarPoint.Web.Spread.IconSetConditionalFormattingRule rule3 = new FarPoint.Web.Spread.IconSetConditionalFormattingRule(FarPoint.Web.Spread.ConditionalFormattingIconSetStyle.ThreeColoredArrows);
      cf3.Add(rule3);
      sheet.ConditionalFormatting.Add(cf3);
    }
  }
}